﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Xml.Linq;
using System.Text;
using Settings;
using System.Data.SqlClient;
using System.Data;
using Utils;
using System.Collections;

namespace Business
{
    public class AdvertismentRepositiory
    {
        CollectionRepository collectionRepository = new CollectionRepository();
       
        //Lấy ra các crm_advertisment hợp lệ
        private List<crm_advertisment> AdvGetAll(SqlCommand cmd)
        {
            try
            {
                string query = string.Format("SELECT * FROM crm_advertisment WHERE (status = '{0}' OR status = '{1}') AND (max_advertisment > sent_advertisment)",
                                            Status.APPROVED, Status.RUNNING);
                cmd.CommandText = query;
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Clear();
                using (SqlDataReader rd = cmd.ExecuteReader())
                {
                    return ObjectClass.CreateList<crm_advertisment>(rd);
                }
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public void AdvertismentExecute()
        {
            try
            {
                var now = DateTime.Now;
                var timenow = now - new DateTime(now.Year, now.Month, now.Day);
                using (SqlConnection conn = new SqlConnection(LocalSettings.ConnectionString))
                {
                    conn.Open();
                    using (SqlCommand cmd = conn.CreateCommand())
                    {
                        foreach (var adv in AdvGetAll(cmd))
                        {
                            //Kiểm tra các điều kiện hợp lệ
                            if (adv.start_expected <= now && adv.end_expected >= now)
                            {
                                if (adv.allow_sent_from <= timenow && adv.allow_sent_to >= timenow)
                                {
                                    //Nếu trạng thái là APPROVED thì chuyển thành RUNNING
                                    if (adv.status == Status.APPROVED)
                                        adv.UpdateStatus(Status.RUNNING, cmd);
                                    if (adv.action_id == (int)ActionId.WhenMessageReceived)
                                    {
                                        SendSMSWhenMessageReceived(adv, cmd);
                                    }
                                    else if (adv.action_id == (int)ActionId.SchedulingSentSMS)
                                    {
                                        SchedulingSentSMS(adv, cmd);
                                    }
                                }
                            }
                            else if (adv.end_expected < now)
                            {
                                if (adv.action_id == (int)ActionId.WhenMessageReceived)
                                {
                                    adv.UpdateStatus(Status.FINISHED, cmd);
                                }
                            }
                        }
                    }
                }
            }
            catch (Exception e)
            {
                new LogRepository().InsertLog(e);
            }
        }

        private void SendSMSWhenMessageReceived(crm_advertisment adv, SqlCommand cmd)
        {
            try
            {
                var collection = collectionRepository.CollectionGetById(adv.collection_id);
                if (collection != null && collection.collection_value != null && collection.enabled.Value)
                {
                    string query = TransformQuery(collection.collection_value, Constants.BatchSize);
                    using (DataTable dtMtQueue = ObjectClass.CreateTableSchema<mt_queue>(), dtServiceSubcriber = new DataTable())
                    {
                        dtMtQueue.PrimaryKey = new DataColumn[] { dtMtQueue.Columns["mt_queue_id"] };
                        dtServiceSubcriber.Columns.Add("guid_key", typeof(Guid));
                        dtServiceSubcriber.Columns.Add("service_id", typeof(int));
                        dtServiceSubcriber.Columns.Add("subscriber",typeof(string));
                        dtServiceSubcriber.Columns.Add("vasp_num", typeof(string));
                        dtServiceSubcriber.Columns.Add("count", typeof(int)).DefaultValue = 1;
                        dtServiceSubcriber.PrimaryKey = new DataColumn[]{
                            dtServiceSubcriber.Columns["service_id"],
                            dtServiceSubcriber.Columns["subscriber"],
                            dtServiceSubcriber.Columns["vasp_num"]
                        };
                        int readerCount = 0;
                        do
                        {
                            readerCount = 0;
                            dtMtQueue.Rows.Clear();
                            dtServiceSubcriber.Rows.Clear();
                            cmd.CommandText = query;
                            cmd.CommandType = CommandType.Text;
                            cmd.Parameters.Clear();
                            using (SqlDataReader reader = cmd.ExecuteReader())
                            {
                                while (reader.Read())
                                {
                                    readerCount++;
                                    if (AddValue_dtServiceSubcriber(dtServiceSubcriber, reader))
                                    {
                                        MtQueueRepository.AddDataRow(dtMtQueue, adv, reader);
                                    }
                                }
                            }
                            if (dtMtQueue.Rows.Count > 0)
                            {
                                int msgsent = MtQueueRepository.Insert(dtMtQueue, adv.max_advertisment - adv.sent_advertisment, cmd);
                                foreach (DataRow row in dtMtQueue.Rows)
                                {
                                    if (!row["status"].Equals(Status.SENT_GATEWAY))
                                    {
                                        DataRow removeRow = dtServiceSubcriber.Rows
                                                            .OfType<DataRow>()
                                                            .FirstOrDefault(p => p["guid_key"].Equals(row["subscriber_classification_guidkey"]));
                                        if (removeRow != null)
                                        {
                                            dtServiceSubcriber.Rows.Remove(removeRow);
                                        }
                                    }
                                }
                                ServiceSubscriberRepository.Update(dtServiceSubcriber.DefaultView.ToTable(false, "service_id", "subscriber", "vasp_num", "count"), cmd);
                                adv.UpdateSent(msgsent, cmd);
                            }
                        }
                        while (readerCount > 0 && (adv.max_advertisment > adv.sent_advertisment));
                    }
                }
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        private void SchedulingSentSMS(crm_advertisment adv, SqlCommand cmd)
        {
            try
            {
                var collection = collectionRepository.CollectionGetById(adv.collection_id);
                if (collection != null && collection.collection_value != null && collection.enabled.Value)
                {
                    int totalRow = GetTotalRow(collection.collection_value, cmd);
                    if (totalRow > 0)
                    {
                        int skip = 0;
                        int batchsize = Constants.BatchSize;
                        int readerCount = 0;
                        Hashtable ht = new Hashtable();
                        using (DataTable dtMtQueue = ObjectClass.CreateTableSchema<mt_queue>())
                        {
                            dtMtQueue.PrimaryKey = new DataColumn[] { dtMtQueue.Columns["mt_queue_id"] };
                            do
                            {
                                readerCount = 0;
                                cmd.CommandText = GetQuery(collection.collection_value, batchsize, skip);
                                cmd.CommandType = CommandType.Text;
                                cmd.Parameters.Clear();
                                dtMtQueue.Rows.Clear();
                                using (SqlDataReader reader = cmd.ExecuteReader())
                                {
                                    while (reader.Read())
                                    {
                                        readerCount++;
                                        var keys = string.Format("{0}{1}{2}{3}", reader["subscriber"], reader["operator_id"], reader["gateway_id"], reader["vasp_num"]);
                                        if (!ht.Contains(keys))
                                        {
                                            ht.Add(keys, null);
                                            MtQueueRepository.AddDataRow(dtMtQueue, adv, reader);
                                        }
                                    }
                                }
                                if (dtMtQueue.Rows.Count > 0)
                                {
                                    int msgsent = MtQueueRepository.Insert(dtMtQueue, adv.max_advertisment - adv.sent_advertisment, cmd);
                                    adv.UpdateSent(msgsent, cmd);
                                }
                                skip += batchsize;
                            }
                            while (readerCount > 0 && skip < totalRow && (adv.max_advertisment > adv.sent_advertisment));
                        }
                    }
                }
                adv.UpdateStatus(Status.FINISHED, cmd);
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        private int GetTotalRow(XElement element, SqlCommand cmd)
        {
            object retValue;
            string query = "SELECT COUNT(*) FROM " + element.Element("from").Value + "\n";
            string whereClause = element.Element("where").Value;
            if (!string.IsNullOrEmpty(whereClause))
                query += "WHERE " + whereClause;
            cmd.CommandText = query;
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Clear();
            retValue = cmd.ExecuteScalar();
            if (retValue == null || retValue.Equals(DBNull.Value)) return 0;
            return (int)retValue;
        }

        private string GetQuery(XElement element, int? batchSize)
        {
            StringBuilder retValue = new StringBuilder("SELECT ");
            string selectClause = element.Element("select").Value;
            string fromClause = element.Element("from").Value;
            string whereClause = element.Element("where").Value;
            if (batchSize != null)
                retValue.Append(string.Format("TOP({0}) ", batchSize));
            retValue.AppendLine(selectClause);
            retValue.AppendLine("FROM " + fromClause);
            if (!string.IsNullOrEmpty(whereClause))
                retValue.AppendLine("WHERE " + whereClause);
            return retValue.ToString();
        }

        private string TransformQuery(XElement element, int? batchSize)
        {
            StringBuilder retValue = new StringBuilder("DELETE ");
            string selectClause = element.Element("select").Value;
            string fromClause = element.Element("from").Value;
            string whereClause = element.Element("where").Value;
            if (batchSize != null)
                retValue.Append(string.Format("TOP({0}) ", batchSize));
            retValue.AppendLine("FROM " + fromClause);
            selectClause = ("DELETED. " + selectClause).Replace(",", ", DELETED.");
            retValue.AppendLine("OUTPUT " + selectClause);
            if (!string.IsNullOrEmpty(whereClause))
                retValue.AppendLine("WHERE " + whereClause);
            return retValue.ToString();
        }

        private string GetQuery(XElement element, int? batchSize, int? skip)
        {
            if (batchSize == null)
            {
                return GetQuery(element, null);
            }
            else
            {
                StringBuilder retValue = new StringBuilder("SELECT ");
                string selectClause = element.Element("select").Value;
                string fromClause = element.Element("from").Value;
                string whereClause = element.Element("where").Value;
                retValue.AppendLine(selectClause);
                retValue.AppendLine("FROM (");
                retValue.AppendLine("SELECT ROW_NUMBER() OVER (ORDER BY seq_id) AS [ROW_NUMBER],");
                retValue.AppendLine(selectClause);
                retValue.AppendLine("FROM " + fromClause + " AS [t0]");
                if (!string.IsNullOrEmpty(whereClause))
                    retValue.AppendLine("WHERE " + whereClause);
                retValue.AppendLine(") AS [t1]");
                retValue.AppendLine(string.Format("WHERE [t1].[ROW_NUMBER] BETWEEN {0} AND {1}", skip + 1, skip + batchSize));
                retValue.AppendLine("ORDER BY [t1].[ROW_NUMBER]");
                return retValue.ToString();
            }
        }

        private bool AddValue_dtServiceSubcriber(DataTable dtServiceSubcriber, SqlDataReader reader)
        {
            object service_id = reader["service_id"].Equals(DBNull.Value) ? -1 : reader["service_id"];
            var keys = new object[] { service_id, reader["subscriber"], reader["vasp_num"] };
            if (!dtServiceSubcriber.Rows.Contains(keys))
            {
                dtServiceSubcriber.Rows.Add(reader["guid_key"], service_id, reader["subscriber"], reader["vasp_num"], 1);
                return true;
            }
            return false;
        }
    }
}
